package Tra

import org.apache.spark.sql.SparkSession

object t4 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().getOrCreate()

    spark.sql(
      """
        |from(from(from hhd.tra
        |   select
        |       car,
        |       speed,
        |       cast(to_timestamp(actionId,'yyyy-MM-dd HH:mm:ss') as bigint) actionTime,
        |       concat_ws('_',monitorId,lead(monitorId,1) over(partition by car order by actionId)) monitorRage)
        |select
        |   car,
        |   count(car) over(partition by car,monitorRage) speedCount,
        |   concat(round((abs(speed - lead(speed,1) over(partition by car,monitorRage order by actionTime))/sum(lead(actionTime,1) over(partition by car order by actionTime) - actionTime) over(partition by car,monitorRage))*100,2),'%') speedRate,
        |   monitorRage)
        |select
        |   *
        |where speedRate is not null
        |""".stripMargin)

//    count(car) over(partition by car)/sum(cast(actionId as bigint) - cast(lead(actionId,1) over(partition by car order by actionId) as bigint)) speedRate,
//    count(car) over(partition by car) speedCount,
  }
}
